﻿CREATE PROCEDURE [dbo].[ListProjectsByStatus]
@StatusIdList VARCHAR (30)
AS
DECLARE @Statuses TABLE (StatusId INT)
DECLARE @Continue BIT

IF LEN(@StatusIdList) > 0 SET @Continue = 1
ELSE SET @Continue = 1
 
WHILE @Continue = 1
BEGIN
	DECLARE @StatusId INT
	IF CHARINDEX(',', @StatusIdList) > 0
	BEGIN
		SET @StatusId = CONVERT(INT, SUBSTRING(@StatusIdList, 1, (CHARINDEX(',', @StatusIdList)-1)))
		SET @StatusIdList = RIGHT(@StatusIdList, (LEN(CONVERT(VARCHAR(5), @StatusId))))

		IF LEN(LTRIM(RTRIM(@StatusIdList))) > 0 SET @Continue = 1
		ELSE SET @Continue = 0
	END
	ELSE
	BEGIN
		SET @StatusId = CONVERT(INT, LTRIM(RTRIM(@StatusIdList)))
		SET @Continue = 0
	END
	INSERT INTO @Statuses (StatusId)
	VALUES (@StatusId)
END

SELECT ProjectID, 
	Name as ProjectName, 
	Description, 
	ManagerUserID, 
	UserName,
	EstCompletionDate, 
	EstDuration,
	ProjectTypeID,
	COALESCE(StatusID, 1) AS StatusID,
	COALESCE(BilledAsOf, '1/1/2000') AS BilledAsOf
FROM Projects
	INNER JOIN Users ON ManagerUserID = UserID
WHERE CONVERT(VARCHAR(3), StatusID) IN(SELECT StatusId FROM @Statuses)
ORDER BY Name

